import pymysql

# coding:utf-8
import pandas as pd
import csv
import re
import numpy as np
from datetime import datetime

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
# cursor.execute("create database dbtest charset=utf8;")
# 使用 fetchone() 方法获取单条数据

'''
sql="""
     	SELECT shop,order2, product_xg,product_ID, product_BianMa,daren ,fuai,dianzhuguo, SUM(Product_num), SUM(Product_price), SUM(yh_pingtai) , SUM(yh_daren) from doudian_day where  month(day)=2 and
     ( order_zt="已签收"  OR order_zt="待发货"  OR order_zt="已完成"  OR order_zt="已发货"  OR order_zt="已支付")
     AND order_sh != "退款成功"  AND order_sh != "售后完成"  AND order_sh != "退款完成"  AND order_sh != "已全额退款" 
		 group by shop,qudao, product_xg,product_ID,product_BianMa,daren ,fuai,dianzhuguo
     """
cursor.execute(sql)

select shop, zqudao, product_xg,product_ID, product_BianMa,daren ,fuai,dianzhuguo, SUM(Product_num), SUM(Product_price), SUM(yh_pingtai) , SUM(yh_daren) from 
(SELECT order2,shop, product_xg,product_ID, product_BianMa,daren ,fuai,dianzhuguo, Product_num, Product_price, yh_pingtai , yh_daren from doudian_day where  month(day)=2 and
     ( order_zt="已签收"  OR order_zt="待发货"  OR order_zt="已完成"  OR order_zt="已发货"  OR order_zt="已支付")
     AND order_sh != "退款成功"  AND order_sh != "售后完成"  AND order_sh != "退款完成"  AND order_sh != "已全额退款" )
LEFT JOIN

( SELECT orderid2, zqudao from orderid_qd where MONTH(day)=2 )
on order2 = orderid2  

group by shop,zqudao, product_xg,product_ID,product_BianMa,daren ,fuai,dianzhuguo

result=cursor.fetchall()
xs=pd.DataFrame(list(result))


xs.columns=['店铺','渠道','选购商品','商品ID','商家编码','达人','福袋','电煮锅','商品数量','应付金额','平台优惠','达人优惠']
xs.to_excel(r'G:\工作\SKU利润测算\2025年\4月\4月利润测算明细_抖音1.xlsx')
'''

sql1 = """
       SELECT shop, product_BianMa, SUM(Product_num), SUM(Product_price), SUM(yh_pingtai)
       from niandu_day
       where month (day)=9
         and
           (order_zt="完成"
          OR order_zt="等待出库"
          OR order_zt="等待确认收货"
          OR order_zt="已签收"
          OR order_zt="待配货"
          OR order_zt="待发货"
          OR order_zt="交易成功"
          OR order_zt="已完成"
          OR order_zt="卖家已发货，等待买家确认"
          OR order_zt="买家已付款，等待卖家发货"
          OR order_zt="已发货"
          OR order_zt="调度中"
          OR order_zt="已收货"
          OR order_zt="已发货未签收"
          OR order_zt="已发货，待签收"
          OR order_zt="(锁定)等待确认收货"
          OR order_zt="已发货，待收货"
          OR order_zt="(锁定)等待确认收货"
          OR order_zt="部分发货"
          OR order_zt="买家已付款,等待卖家发货"
          OR order_zt="卖家已发货"
          OR order_zt="买家已付款"
          OR order_zt="发货即将超时"
          OR order_zt="卖家部分发货"
          OR order_zt="待买家收货"
          OR order_zt="待卖家发货"
          OR order_zt="部分发货中")
         AND order_sh != "退款成功"
         AND order_sh != "售后完成"
         AND order_sh != "退款完成"
         AND order_sh != "退货退款完成 "
       group by shop, product_BianMa \
       """

cursor.execute(sql1)
result1 = cursor.fetchall()
xs_xs = pd.DataFrame(list(result1))
xs_xs.columns = ['店铺', '编码', '商品数量', '应付', '平台优惠']
xs_xs.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月利润测算明细_线上.xlsx')

sql11 = """
        SELECT *
        from doudian_day
        where month (day)=9
          and dianzhuguo="电煮锅赠品"
          and
            ( order_zt="已签收"
           OR order_zt="待发货"
           OR order_zt="已完成"
           OR order_zt="已发货"
           OR order_zt="已支付")
          AND order_sh != "退款成功"
          AND order_sh != "售后完成"
          AND order_sh != "退款完成"
          AND order_sh != "已全额退款" \
 \
        """
cursor.execute(sql11)
result11 = cursor.fetchall()
dg = pd.DataFrame(list(result11))
# dg.columns=['店铺','编码','商品数量','应付','平台优惠']
dg.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月锅.xlsx')

sql2 = """
       select product_BianMa, SUM(Product_num), SUM(Product_price), SUM(yh_pingtai), SUM(yh_daren)
       from doudian_day
       where month (day)=9
         and
           ( order_zt="已签收"
          OR order_zt="待发货"
          OR order_zt="已完成"
          OR order_zt="已发货"
          OR order_zt="已支付")
         AND order_sh != "退款成功"
         AND order_sh != "售后完成"
         AND order_sh != "退款完成"
         AND order_sh != "已全额退款"
       group by product_BianMa \
 \
       """

sql22 = """
        select product_BianMa, Product_dj, SUM(Product_num), SUM(Product_price), SUM(yh_pingtai)
        from doudian_day
        where month (day)=9
          and
            ( order_zt="已签收"
           OR order_zt="待发货"
           OR order_zt="已完成"
           OR order_zt="已发货"
           OR order_zt="已支付")
          AND order_sh != "退款成功"
          AND order_sh != "售后完成"
          AND order_sh != "退款完成"
          AND order_sh != "已全额退款"
        group by product_BianMa, Product_dj \
 \
        """

cursor.execute(sql2)
result2 = cursor.fetchall()

jg = pd.DataFrame(list(result2))
jg.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月抖音编码价格.xlsx')

cursor.execute(sql22)
result22 = cursor.fetchall()

dj = pd.DataFrame(list(result22))
dj.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月抖音编码单价.xlsx')
